var fs = require('fs');
var config = require('../config');
var tools = require(__dirname + '/tools');
var mysql = require('mysql');
var connection = mysql.createConnection(config.mysql);
//connection.connect();
const crypto = require('crypto');

var pool = mysql.createPool(config.mysql);

pool.on('connection', function (connection) {
    connection.query("SET sql_mode=''");
});

var redis = require(tools.rootPath + 'libs/redis');

var model = {
    debug: false,
    init: function () {
        //console.log('model.init');
    },
    //logerr
    log_error: function (err, data = '') {
        let str = err.errno + ':' + err.sqlMessage + "\n" + err.sql + "\n" + JSON.stringify(data) + "\n";
        fs.appendFile(tools.runtimePath + 'mysql.txt', str, (err) => {
            console.error('sql error:', str);
        });
    },
    //检查文件是否存在
    file_exists: async function (file) {
        return new Promise((resolve, reject) => {
            fs.readFile(file, 'utf8', (err, data) => {
                if (err) {
                    resolve([false, null]);
                    return;
                }
                resolve([true, data]);
            });
        });
    },
    //table_fields
    table_fields: async function (table, type = 'file') {
        if (type == 'file') {
            //check exist
            let jsonfile = tools.tableCachePath + table + '.json';
            let myfile = await this.file_exists(jsonfile);
            if (myfile[0] === true) {
                try {
                    return JSON.parse(myfile[1]);
                } catch (e) {
                    return [];
                }
            }

            return new Promise((resolve, reject) => {
                pool.query("select COLUMN_NAME from  information_schema.COLUMNS where TABLE_SCHEMA=? and TABLE_NAME=?", [config.mysql.database, table], (err, results) => {
                    if (err) {
                        try {
                            this.log_error(err, [table]);
                        } catch (e) { }

                        resolve([]);
                        return;
                    }
                    let r = [];
                    for (let i = 0; i < results.length; i++) {
                        r.push(results[i]['COLUMN_NAME']);
                    }
                    fs.writeFile(jsonfile, JSON.stringify(r), 'utf8', (err) => {
                        if (err) {
                            console.error('save table jsonfile fail!');
                        }
                    });
                    resolve(r);
                });
            });
        }

        if (type == 'redis') {
            let cacheTime = 86400;
            let sql = "select COLUMN_NAME from  information_schema.COLUMNS where TABLE_SCHEMA=? and TABLE_NAME=?";
            let p = [conf.mysql.database, table];
            let sign = 'select' + this.getSign(sql, p, cacheTime);

            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];

            return new Promise(async (resolve, reject) => {
                pool.query(sql, p, async (err, results) => {
                    if (err) {
                        try {
                            this.log_error(err, [table]);
                        } catch (e) { }

                        resolve([]);
                        return;
                    }
                    let r = [];
                    for (let i = 0; i < results.length; i++) {
                        r.push(results[i]['COLUMN_NAME']);
                    }
                    await this.setCache(sign, r, cacheTime, type);
                    resolve(r);
                });
            });
        }
    },
    //where
    where: function (obj, before = '') {
        if (Object.keys(obj).length == 0) return before;
        let sql = [];
        if (before) sql.push(before);

        for (let k in obj) {
            let v = obj[k];
            if (v instanceof Array) {
                if (v.length != 2) continue;

                if (v[0].toLowerCase() == 'like') {
                    sql.push(` ${k} like '${v[1]}' `);
                    continue;
                }

                if (v[0].toLowerCase() == 'in') {
                    if (!(v[1] instanceof Array)) {
                        v[1] = [v[1]];
                    }
                    v[1].push(0);
                    v[1] = v[1].join(',');
                    sql.push(` ${k} in(${v[1]}) `);
                    continue;
                }

                if (v[0].toLowerCase() == 'not in') {
                    if (!(v[1] instanceof Array)) {
                        v[1] = [v[1]];
                    }
                    v[1].push(0);
                    v[1] = v[1].join(',');
                    sql.push(` ${k} not in(${v[1]}) `);
                    continue;
                }

                if (v[0].toLowerCase() == 'exp') {
                    sql.push(` ${v[1]} `);
                    continue;
                }

                sql.push(` ${k} ${v[0]} '${v[1]}' `);
                continue;
            }
            sql.push(` ${k}='${v}' `);
        }

        return sql.join(' and ');
    },
    //escape
    escape: function (value) {
        return mysql.escape(value);
    },
    //insert
    insert: function (sql, p = []) {
        return new Promise((resolve, reject) => {
            pool.query(sql, p, (err, results, fields) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve(false);
                    return;
                }
                resolve(results.insertId);
            });
        });
    },
    //insert into table set ?
    i: async function (sql, p = {}) {
        let table = sql.match(/insert into(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is)[1];
        if (!table) return false;
        let fields = await this.table_fields(table);

        let p2 = {};
        for (var i in p) {
            if (fields.indexOf(i) > -1) p2[i] = p[i];
        }
        return new Promise((resolve, reject) => {
            pool.query(sql, p2, (err, results, fields) => {
                if (err) {
                    try {
                        this.log_error(err, p2);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve(false);
                    return;
                }
                resolve(results.insertId);
            });
        });
    },
    //update
    update: function (sql, p = []) {
        return new Promise((resolve, reject) => {
            pool.query(sql, p, (err, results, fields) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve(false);
                    return;
                }
                resolve(true);
            });
        });
    },
    //update table set ? where id=?
    u: async function (sql, p = {}, where = []) {
        let table = sql.match(/update(?:\s)*(?:`)?([a-z0-9_-]+)(?:`)?(?:\s)*.*/is)[1];
        if (!table) return false;
        let fields = await this.table_fields(table);

        let p2 = {};
        for (var i in p) {
            if (fields.indexOf(i) > -1) p2[i] = p[i];
        }
        where.unshift(p2);
        return new Promise((resolve, reject) => {
            pool.query(sql, where, (err, results, fields) => {
                if (err) {
                    try {
                        this.log_error(err, where);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve(false);
                    return;
                }
                resolve(true);
            });
        });
    },
    //delete
    delete: function (sql, p = []) {
        return new Promise((resolve, reject) => {
            pool.query(sql, p, (err, results, fields) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve(false);
                    return;
                }
                resolve(true);
            });
        });
    },
    //http://nodejs.cn/api/fs.html
    //https://blog.csdn.net/jiaoqi6132/article/details/104180140
    //获取md5
    getSign: function (sql, p, cacheTime) {
        return cacheTime > 0 ? crypto.createHash('md5').update(`${sql}${JSON.stringify(p)}`).digest('hex') : '';
    },
    //读取缓存
    getCache: async function (sign, cacheTime, type) {
        if (type == 'file') {
            let cachePath = tools.runtimePath + 'cache/';
            let subFolderA = sign.substr(0, 2);
            let subFolderB = sign.substr(2, 2);
            let dest = cachePath + subFolderA + '/' + subFolderB + '/' + sign + '.json';
            try {
                let e = await fs.promises.stat(dest);
                let mTime = parseInt(e.mtimeMs / 1000);
                let now = parseInt(new Date().getTime() / 1000);
                if (now > mTime + cacheTime) return [false, null];
                let d = JSON.parse(await fs.promises.readFile(dest, 'utf8'));
                return [true, d];
            } catch (err) {
                if (err) return [false, null];
            }
        }
        if (type == 'redis') {
            try {
                let d = JSON.parse(await redis.get(sign));
                if (d === null) return [false, null];
                return [true, d];
            } catch (err) {
                if (err) return [false, null];
            }
        }
        return [false, null];
    },
    //设置缓存
    setCache: async function (sign, data, cacheTime, type) {
        if (type == 'file') {
            let cachePath = tools.runtimePath + 'cache/';
            let subFolderA = sign.substr(0, 2);
            let subFolderB = sign.substr(2, 2);
            let dest = cachePath + subFolderA + '/' + subFolderB + '/' + sign + '.json';
            try {
                await fs.promises.mkdir(cachePath + subFolderA + '/' + subFolderB + '/', { recursive: true });
                await fs.promises.writeFile(dest, JSON.stringify(data));
                return true;
            } catch (err) {
                if (err) return false;
            }
        }
        if (type == 'redis') {
            try {
                await redis.set(sign, JSON.stringify(data), "EX", cacheTime);
                return true;
            } catch (err) {
                if (err) return false;
            }
        }
        return true;
    },
    //删除缓存
    deleteCache: async function (sign, type) {
        if (type == 'file') {
            let cachePath = tools.runtimePath + 'cache/';
            let subFolderA = sign.substr(0, 2);
            let subFolderB = sign.substr(2, 2);
            let dest = cachePath + subFolderA + '/' + subFolderB + '/' + sign + '.json';
            try {
                await fs.promises.unlink(dest);
                return true;
            } catch (err) {
                if (err) return false;
            }
        }
        if (type == 'redis') {
            try {
                await redis.del(sign);
                return true;
            } catch (err) {
                if (err) return false;
            }
        }
        return true;
    },
    recursion_scan_keys: async function (n, pattern, results) {
        let [cursor, keys] = await redis.scan(n, 'MATCH', pattern);

        results.push.apply(results, keys);

        if (cursor === '0') {
            return;
        }
        await this.recursion_scan_keys(cursor, pattern, results);
        return;
    },
    //按模式删除部分缓存
    clearSomeCache: async function (pattern, type) {
        if (type == 'file') {
            return true;
        }
        if (type == 'redis') {
            try {
                let results = [];
                await this.recursion_scan_keys(0, pattern, results);
                await redis.del(results);
                return true;
            } catch (err) {
                if (err) return false;
            }
        }
    },
    //handle limit
    handle_limit: function (sql) {
        if (sql.indexOf('select') == -1) {
            return sql;
        }
        if (sql.indexOf('limit 1') > -1) {
            return sql;
        }
        return sql + ' limit 1';
    },
    //multi
    multi: function (cacheTime = 0, type = 'file') {
        let model = this;
        return {
            cacheTime: cacheTime,
            type: type,
            sqls: [],
            data: [],
            kind: [],
            push: function (sql, data = [], kind = 'select') {
                if (kind == 'find' || kind == 'value') {
                    sql = model.handle_limit(sql);
                }
                this.sqls.push(sql);
                this.data = this.data.concat(data);
                this.kind.push(kind);
                return this;
            },
            exec: async function () {
                if (this.sqls.length < 2) return false;
                let sql = this.sqls.join(';');
                let p = this.data;
                let cacheTime = this.cacheTime;
                let type = this.type;
                let sign = 'multi' + model.getSign(sql, p, cacheTime);
                if (cacheTime > 0) {
                    let d = await model.getCache(sign, cacheTime, type);
                    if (d[0] === true) return d[1];
                }
                return new Promise(async (resolve, reject) => {
                    pool.query(sql, p, async (err, results) => {
                        if (err) {
                            try {
                                model.log_error(err, p);
                            } catch (e) { }
                            if (model.debug) console.error('sql_error:', sql);
                            resolve([]);
                            return;
                        }
                        let final_data = [];
                        for (let [index, d] of results.entries()) {
                            switch (this.kind[index]) {
                                case 'select':
                                    final_data.push(d.length > 0 ? d : []);
                                    break;
                                case 'find':
                                    final_data.push(d.length > 0 ? d[0] : {});
                                    break;
                                case 'column':
                                    let r = [];
                                    for (let i = 0; i < d.length; i++) {
                                        for (let n in d[i]) {
                                            r.push(d[i][n]);
                                            break;
                                        }
                                    }
                                    final_data.push(r);
                                    break;
                                case 'value':
                                    let r2 = '';
                                    if (d.length > 0) {
                                        for (let n in d[0]) {
                                            r2 = d[0][n];
                                            break;
                                        }
                                    }
                                    final_data.push(r2);
                                    break;
                                case 'map':
                                    let r3 = {};
                                    for (let i = 0; i < d.length; i++) {
                                        let fds = [];
                                        for (let n in d[i]) {
                                            fds.push(n);
                                        }
                                        r3[d[i][fds[0]]] = d[i][fds[1]];
                                    }
                                    final_data.push(r3);
                                    break;
                            }
                        }
                        if (cacheTime > 0) {
                            await model.setCache(sign, final_data, cacheTime, type);
                        }
                        resolve(final_data);
                    });
                });
            },
        };
    },
    //batch
    batch: async function (kind, labelName, masterUniqueField, slaveUniqueField, sql, data, func, joinType = 'in', cacheTime = 0, type = 'file') {
        if (!data.length) return [];
        let sqls = [];
        let params = [];
        let big_sql = '';
        if (joinType == 'union') {
            let tmp = [];
            for (let [i, d] of data.entries()) {
                if (tmp.indexOf(d[masterUniqueField]) > -1) continue;
                sqls.push('(' + sql + ')');
                params = params.concat(func(d));
                tmp.push(d[masterUniqueField]);
            }
            big_sql = sqls.join(' union ');
        } else {
            big_sql = sql;
            params = params.concat(func(data));
        }

        let sign = 'batch' + this.getSign(big_sql, params, cacheTime);
        if (cacheTime > 0) {
            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];
        }
        return new Promise(async (resolve, reject) => {
            pool.query(big_sql, params, async (err, results, fields) => {
                if (err) {
                    if (this.debug) console.error('sql_error:', big_sql);
                    this.log_error(err, params);
                    resolve([]);
                    return;
                }
                let FDS = fields.map((v) => v.name);
                if (FDS.indexOf(slaveUniqueField) == -1) {
                    throw ('sql字段中没有' + slaveUniqueField);
                }

                let map = {};
                for (let [index, d] of results.entries()) {
                    switch (kind) {
                        case 'select':
                            if (typeof (map[d[slaveUniqueField]]) == 'undefined') {
                                map[d[slaveUniqueField]] = [];
                            }
                            map[d[slaveUniqueField]].push(d);
                            break;
                        case 'find':
                            if (typeof (map[d[slaveUniqueField]]) == 'undefined') {
                                map[d[slaveUniqueField]] = d;
                            }
                            break;
                        case 'column':
                            if (typeof (map[d[slaveUniqueField]]) == 'undefined') {
                                map[d[slaveUniqueField]] = [];
                            }
                            map[d[slaveUniqueField]].push(d[FDS[0]]);
                            break;
                        case 'value':
                            if (typeof (map[d[slaveUniqueField]]) == 'undefined') {
                                map[d[slaveUniqueField]] = d[FDS[0]];
                            }
                            break;
                        case 'map':
                            if (typeof (map[d[slaveUniqueField]]) == 'undefined') {
                                map[d[slaveUniqueField]] = {};
                            }
                            map[d[slaveUniqueField]][d[FDS[0]]] = d[FDS[1]];
                            break;
                    }
                }

                for (let [index, d] of data.entries()) {
                    switch (kind) {
                        case 'select':
                            d[labelName] = typeof (map[d[masterUniqueField]]) != 'undefined' ? map[d[masterUniqueField]] : [];
                            break;
                        case 'find':
                            d[labelName] = typeof (map[d[masterUniqueField]]) != 'undefined' ? map[d[masterUniqueField]] : {};
                            break;
                        case 'column':
                            d[labelName] = typeof (map[d[masterUniqueField]]) != 'undefined' ? map[d[masterUniqueField]] : [];
                            break;
                        case 'value':
                            d[labelName] = typeof (map[d[masterUniqueField]]) != 'undefined' ? map[d[masterUniqueField]] : '';
                            break;
                        case 'map':
                            d[labelName] = typeof (map[d[masterUniqueField]]) != 'undefined' ? map[d[masterUniqueField]] : {};
                            break;
                    }
                }

                if (cacheTime > 0) {
                    await this.setCache(sign, data, cacheTime, type);
                }
                resolve(data);
            });
        });
    },
    //query
    query: async function (sql, p = [], cacheTime = 0, type = 'file', customMark = '') {
        let sign = !customMark ? 'query' + this.getSign(sql, p, cacheTime) : customMark;
        if (cacheTime > 0) {
            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];
        }
        return new Promise(async (resolve, reject) => {
            pool.query(sql, p, async (err, results) => {
                if (err) {
                    if (this.debug) console.error('sql_error:', sql);
                    this.log_error(err, p);
                    resolve(null);
                    return;
                }
                if (cacheTime > 0) {
                    await this.setCache(sign, results, cacheTime, type);
                }
                resolve(results);
            });
        });
    },
    //select
    select: async function (sql, p = [], cacheTime = 0, type = 'file', customMark = '') {
        let sign = !customMark ? 'select' + this.getSign(sql, p, cacheTime) : customMark;
        if (cacheTime > 0) {
            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];
        }
        return new Promise(async (resolve, reject) => {
            pool.query(sql, p, async (err, results) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve([]);
                    return;
                }
                if (cacheTime > 0) {
                    await this.setCache(sign, results.length > 0 ? results : [], cacheTime, type);
                }
                resolve(results.length > 0 ? results : []);
            });
        });
    },
    //find
    find: async function (sql, p = [], cacheTime = 0, type = 'file', customMark = '') {
        sql = this.handle_limit(sql);
        let sign = !customMark ? 'find' + this.getSign(sql, p, cacheTime) : customMark;
        if (cacheTime > 0) {
            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];
        }
        return new Promise(async (resolve, reject) => {
            pool.query(sql, p, async (err, results) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve({});
                    return;
                }
                if (cacheTime > 0) {
                    await this.setCache(sign, results.length > 0 ? results[0] : {}, cacheTime, type);
                }
                resolve(results.length > 0 ? results[0] : {});
            });
        });
    },
    //column
    column: async function (sql, p = [], cacheTime = 0, type = 'file', customMark = '') {
        let sign = !customMark ? 'column' + this.getSign(sql, p, cacheTime) : customMark;
        if (cacheTime > 0) {
            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];
        }
        return new Promise(async (resolve, reject) => {
            pool.query(sql, p, async (err, results) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve([]);
                    return;
                }
                let r = [];
                for (let i = 0; i < results.length; i++) {
                    for (let n in results[i]) {
                        r.push(results[i][n]);
                        break;
                    }
                }
                if (cacheTime > 0) {
                    await this.setCache(sign, r, cacheTime, type);
                }
                resolve(r);
            });
        });
    },
    //value
    value: async function (sql, p = [], cacheTime = 0, type = 'file', customMark = '') {
        sql = this.handle_limit(sql);
        let sign = !customMark ? 'value' + this.getSign(sql, p, cacheTime) : customMark;
        if (cacheTime > 0) {
            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];
        }
        return new Promise(async (resolve, reject) => {
            pool.query(sql, p, async (err, results) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve('');
                    return;
                }
                let r = '';
                if (results.length > 0) {
                    for (let n in results[0]) {
                        r = results[0][n];
                        break;
                    }
                    if (cacheTime > 0) {
                        await this.setCache(sign, r, cacheTime, type);
                    }
                    resolve(r);
                    return;
                }
                if (cacheTime > 0) {
                    await this.setCache(sign, r, cacheTime, type);
                }
                resolve(r);
            });
        });
    },
    //map
    map: async function (sql, p = [], cacheTime = 0, type = 'file', customMark = '') {
        let sign = !customMark ? 'map' + this.getSign(sql, p, cacheTime) : customMark;
        if (cacheTime > 0) {
            let d = await this.getCache(sign, cacheTime, type);
            if (d[0] === true) return d[1];
        }
        return new Promise(async (resolve, reject) => {
            pool.query(sql, p, async (err, results) => {
                if (err) {
                    try {
                        this.log_error(err, p);
                    } catch (e) { }
                    if (this.debug) console.error('sql_error:', sql);
                    resolve({});
                    return;
                }
                let r = {};
                for (let i = 0; i < results.length; i++) {
                    let fds = [];
                    for (let n in results[i]) {
                        fds.push(n);
                    }
                    r[results[i][fds[0]]] = results[i][fds[1]];
                }
                if (cacheTime > 0) {
                    await this.setCache(sign, r, cacheTime, type);
                }
                resolve(r);
            });
        });
    },
    //自定义方法
    get_users: function () {
        return ['admin', 'tom', 'jack'];
    }
};

model.init();

module.exports = { connection: connection, pool: pool, model: model };
